<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/10/19
 * Time: 16:16
 */

namespace app\index\table;

use app\index\controller\Login;
use app\index\service\ExcelService;
use think\Db;

class FurnAlterTable extends BaseTable
{
    protected static $table = 'FURN_ALTERINFO';
    protected static $pk = 'ALTER_NO';

    static public function table(){
        return Db::table(static::$table.' fa')
            ->leftJoin('FURN_DICT_ALTERTYPE fd','fd.ALTER_TYPE_ID=fa.ALTERTYPE_ID')
            ->leftJoin('DICT_SB_DEPARTMENT dp','dp.DEPART_ID=fa.DEPARTNEW_ID')
            ->leftJoin('DICT_COLLEGE dc','dc.COLLEGE_ID=dp.DEPART_BELONG')
            ->leftJoin('FURN_INFO f','f.FURN_NO=fa.FURN_NO')
            ->leftJoin('FURN_DICT_STATE ds','ds.STATE_ID=f.STATE_ID')
            ->leftJoin('EMPLOYEE emp','emp.STAFF_ID=f.INPUT_PERSON')
            ->field([
                'fa.ALTER_NO'
                ,'fa.FURN_NO'
                ,'f.FURN_NAME'
                ,'ds.STATE'
                ,'fa.ALTERTYPE_ID'
                ,'fd.ALTER_TYPE'
                ,"to_char(fa.ALTER_TIME,'yyyy-mm-dd') ALTER_TIME"
                ,'fa.ALTER_PRICE'
                ,'fa.CHECK_PERSON'
                ,'fa.CHARGE_PERSON'
                ,'emp.NAME INPUT_NAME'
                ,"to_char(INPUT_DATE,'yyyy-mm-dd') INPUT_DATE"
                ,"fa.REMARK"
                ,"fa.DEPARTNEW_ID"
                ,"dp.DEPART_SHORT"
                ,'dc.COLLEGE_ID'
                ,'dc.SHORTNAME'
                ,'fa.DISPOSE_DIRECT'
                ,'fa.PRE_ALTERINFO_NO'

            ]);

    }

    static public function add($data){
        $data['INPUT_PERSON'] = Login::getUserId();
        $data['INPUT_DATE'] = date('Y-m-d');

        self::toDate($data,'ALTER_TIME');
        self::toDate($data,'INPUT_DATE');
        return Db::table(static::$table)->insert($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function getPreAlterInfoNo(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR(PRE_ALTERINFO_NO,5,4)) as NUM')
            ->where([['SUBSTR(PRE_ALTERINFO_NO,1,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return $year.sprintf("%04d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'预异动单编号','field'=>'PRE_ALTERINFO_NO']
            ,['title'=>'变动编号','field'=>'ALTER_NO']
            ,['title'=>'家具编号','field'=>'FURN_NO']
            ,['title'=>'家具名称','field'=>'FURN_NAME']
            ,['title'=>'状态','field'=>'STATE']
            ,['title'=>'变动类型','field'=>'ALTER_TYPE']
            ,['title'=>'变动时间','field'=>'ALTER_TIME']
            ,['title'=>'变动价格','field'=>'ALTER_PRICE']
            ,['title'=>'经手人','field'=>'CHECK_PERSON']
            ,['title'=>'负责人','field'=>'CHARGE_PERSON']
            ,['title'=>'制单人','field'=>'INPUT_NAME']
            ,['title'=>'制单时间','field'=>'INPUT_DATE']
            ,['title'=>'备注','field'=>'REMARK']
            ,['title'=>'变动前部门','field'=>'DEPART_SHORT']
            ,['title'=>'报废去向','field'=>'DISPOSE_DIRECT']
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }
}